package com.liuhong.shoping.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtils {
	
	/**
 	 *
 	 * 根据传入List数据集合导出Excel表格 返回页面选择保存路径的excel
 	 * @param response （响应页面）
 	 * @param list 查询的一个数据集合列表
 	 * @param columnNames 导出后的excel文件的表头
 	 * @param columns 对应数据库里的属性名
 	 * @param sheetName 工作簿的名字
 	 * @param filename  导出的文件名
 	 */
 	@SuppressWarnings("rawtypes")
 	public static void exportExcel(HttpServletResponse response,List list, String[] columnNames, String[] columns,String sheetName, String filename) {
 		OutputStream fos = null;
 		try {
			//响应输出流，让用户自己选择保存路径
 			fos = getOutputStream(response, filename);

 			HSSFWorkbook wb = new HSSFWorkbook();
 			HSSFSheet sheet = wb.createSheet(sheetName);
 			HSSFCellStyle style = wb.createCellStyle(); // 样式对象
// 			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
// 			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

 			HSSFRow row = sheet.createRow(0);
 			for (int i = 0; i < columnNames.length; i++) {
 				HSSFCell cell = row.createCell(i);
 				cell.setCellValue(columnNames[i]);
 				cell.setCellStyle(style);
 			}
 			for (int i = 0; i < list.size(); i++) {
 				HSSFRow listRow = sheet.createRow(i + 1);
 				Object o = list.get(i);
 				for (int j = 0; j < columns.length; j++) {
 					HSSFCell listCell = listRow.createCell(j);
 					Method m = o.getClass().getMethod("get" + upperStr(columns[j]));
 					Object value =  m.invoke(o);
 					if (value != null) {
 						listCell.setCellValue(value + "");
 						listCell.setCellStyle(style);
 					} else {
 						listCell.setCellValue("");
 						listCell.setCellStyle(style);
 					}
 					sheet.autoSizeColumn(j+1, true);//自适应，从1开始
 				}
 			}
 			wb.write(fos);
 		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			try {
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
 	}
 	
 	/**
 	 * 根据response获取输出流
 	 * @param response
 	 * @param filename 生成文件的文件名称
 	 * @return
 	 * @throws UnsupportedEncodingException
 	 * @throws IOException
 	 */
	public static OutputStream getOutputStream(HttpServletResponse response, String filename){
		OutputStream fos = null;
		response.setCharacterEncoding("UTF-8");
		response.reset();//清除缓存
		//response.setContentType("octets/stream");
		response.setContentType("application/force-download");// 设置强制下载不打开

		try {
			response.addHeader("Content-Disposition", "attachment;filename="+ new String((filename).getBytes("UTF-8"), "iso8859-1")+ ".xls");
			fos = response.getOutputStream();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return fos;
	}
	
	/**
 	 * 把输入字符串的首字母改成大写
 	 * 
 	 * @param str
 	 * @return
 	 */
 	private static String upperStr(String str) {
 		char[] ch = str.toCharArray();
 		if (ch[0] >= 'a' && ch[0] <= 'z') {
 			ch[0] = (char) (ch[0] - 32);
 		}
 		return new String(ch);
 	}

}
